package crm.control;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import crm.dbcoon.DBConnect;
import crm.model.Custom_require;
import crm.model.Recepit;
import crm.util.Pagination;

public class CustomRequieControl {
	public static Pagination pageInstance = null;
	/**
	 * 
	 */
	public static Custom_require getCustomRequireById(int id) {
		DBConnect dbc = new DBConnect();
		String sql = "select * from custom_require where id = '"+id+"'";
		dbc.prepareStatement(sql);
		try {
		    ResultSet rs = dbc.executeQuery();
			while (rs.next()) {
				Custom_require customRequire = new Custom_require();
				 customRequire.setSystem_name(rs.getString("system_name"));
				 customRequire.setRequire_book_id(rs.getString("require_book_id"));
				 customRequire.setModel_name(rs.getString("model_name"));
				 customRequire.setFunction(rs.getString("function"));
				 customRequire.setType(rs.getString("type"));
				 customRequire.setLink_play(rs.getString("link_play"));
				 customRequire.setOffer_depart(rs.getString("offer_depart"));
				 customRequire.setOffer_person(rs.getString("offer_person"));
				 customRequire.setDeserve_depart(rs.getString("deserve_depart"));
				 customRequire.setDeserve_person(rs.getInt("deserve_person"));
				 customRequire.setDescription(rs.getString("description"));
				 customRequire.setDate(rs.getString("date"));
				 return customRequire;
			}
			return null;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 添加客户需求
	 * @param customRequire
	 * @return
	 */
	public static boolean addCustomRequire( Custom_require customRequire) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into custom_require(system_name,require_book_id,model_name,function,type,link_play,offer_depart,offer_person,deserve_depart,deserve_person,description,date) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			int index = 1;
			dbc.setString(index++, customRequire.getSystem_name());
			dbc.setString(index++, customRequire.getRequire_book_id());
			dbc.setString(index++, customRequire.getModel_name());
			dbc.setString(index++, customRequire.getFunction());
			dbc.setString(index++, customRequire.getType());
			dbc.setString(index++, customRequire.getLink_play());
			dbc.setString(index++, customRequire.getOffer_depart());
			dbc.setString(index++, customRequire.getOffer_person());
			dbc.setString(index++, customRequire.getDeserve_depart());
			dbc.setInt(index++, customRequire.getDeserve_person());
			dbc.setString(index++, customRequire.getDescription());
			dbc.setString(index++, customRequire.getDate());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	public static Boolean delCustomRequire(int id) {
		DBConnect dbc = new DBConnect();
		String sql = "delete from custom_require where id = '"+id+"'";
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 更新
	 * @param id
	 * @return
	 */
	public static Boolean updateCustomRequire(Custom_require customRequire) {
		DBConnect dbc = new DBConnect();
		String sql = "update custom_require set system_name = ?,require_book_id = ?,model_name = ?,function = ?,type = ?,link_play = ?,offer_depart = ?,offer_person = ?,deserve_depart = ?,description = ?,date = ? where id = '"+customRequire.getId()+"'";
		dbc.prepareStatement(sql);
		try {
			int index = 1;
			dbc.setString(index++, customRequire.getSystem_name());
			dbc.setString(index++, customRequire.getRequire_book_id());
			dbc.setString(index++, customRequire.getModel_name());
			dbc.setString(index++, customRequire.getFunction());
			dbc.setString(index++, customRequire.getType());
			dbc.setString(index++, customRequire.getLink_play());
			dbc.setString(index++, customRequire.getOffer_depart());
			dbc.setString(index++, customRequire.getOffer_person());
			dbc.setString(index++, customRequire.getDeserve_depart());
			//dbc.setInt(index++, customRequire.getDeserve_person());
			dbc.setString(index++, customRequire.getDescription());
			dbc.setString(index++, customRequire.getDate());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 获取所有需求书
	 * @return
	 */
	public static Collection<Custom_require> getAllRequires() {
		Collection<Custom_require> cr = new ArrayList<Custom_require>();
		DBConnect dbc = new DBConnect();
		String sql = "select * from custom_require";
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		try {
		    rs = dbc.executeQuery();
			while (rs.next()) {
				Custom_require customRequire = new Custom_require();
				 customRequire.setSystem_name(rs.getString("system_name"));
				 customRequire.setRequire_book_id(rs.getString("require_book"));
				 customRequire.setModel_name(rs.getString("model_name"));
				 customRequire.setFunction(rs.getString("function"));
				 customRequire.setType(rs.getString("type"));
				 customRequire.setLink_play(rs.getString("link_play"));
				 customRequire.setOffer_depart(rs.getString("offer_depart"));
				 customRequire.setOffer_person(rs.getString("offer_person"));
				 customRequire.setDeserve_depart(rs.getString("deserve_depart"));
				 customRequire.setDeserve_person(rs.getInt("deserve_person"));
				 customRequire.setDescription(rs.getString("description"));
				 customRequire.setDate(rs.getString("date"));
				 cr.add(customRequire);
			}
			return cr;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 
	 * @param require_book_id
	 * @param offer_depart
	 * @param offer_person
	 * @param deserve_person
	 * @param create_time
	 * @param pages
	 * @param rowPerPage
	 * @return
	 */
	public static Collection<Object> searchCustomRequires(String require_book_id,String offer_depart,String offer_person,String deserve_person,String create_time,int pages,int rowPerPage) {
		if(require_book_id==null||require_book_id.equals(""))
			require_book_id = "%";
		if(offer_depart==null||offer_depart.equals(""))
			offer_depart="%";
		if(offer_person==null||offer_person.equals(""))
			offer_person="%";
		if(deserve_person==null||deserve_person.equals(""))
			deserve_person="%";
		if(create_time==null||create_time.equals(""))
			create_time="%";
		String sql = "select custom_require.* from custom_require,info_employees where custom_require.deserve_person = info_employees.id and info_employees.name like '%"+deserve_person+"%'" +
				"and custom_require.require_book_id like '%"+require_book_id+"%'" +
						"and custom_require.offer_depart like'%"+offer_depart+"%'" +
								"and custom_require.offer_person like '%"+offer_person+"%'" +
										"and custom_require.deserve_person like '%"+deserve_person+"%'" +
												"and custom_require.date like '%"+create_time+"%' order by custom_require.id desc";
		System.out.println(sql);
		pageInstance = new CustomRequireControlPage(sql, rowPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		
	}

}
class CustomRequireControlPage extends Pagination {
	
	public CustomRequireControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	
	protected Collection<Custom_require> packResultSet(ResultSet rs) {
		Collection<Custom_require> cr = new ArrayList<Custom_require>();
		try{
			while(rs.next()){
				 Custom_require customRequire = new Custom_require();
				 customRequire.setId(rs.getInt("id"));
				 customRequire.setSystem_name(rs.getString("system_name"));
				 customRequire.setRequire_book_id(rs.getString("require_book_id"));
				 customRequire.setModel_name(rs.getString("model_name"));
				 customRequire.setFunction(rs.getString("function"));
				 customRequire.setType(rs.getString("type"));
				 customRequire.setLink_play(rs.getString("link_play"));
				 customRequire.setOffer_depart(rs.getString("offer_depart"));
				 customRequire.setOffer_person(rs.getString("offer_person"));
				 customRequire.setDeserve_depart(rs.getString("deserve_depart"));
				 customRequire.setDeserve_person(rs.getInt("deserve_person"));
				 customRequire.setDescription(rs.getString("description"));
				 customRequire.setDate(rs.getString("date"));
				 cr.add(customRequire);
				 customRequire = null; 
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<Custom_require>出错");
		}
		return cr;
	}

}
